SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [Weekly].[GetPropBreakout]
(
    @FundId AS VARCHAR (10),
    @NAVDate AS DATE
) AS
BEGIN
DECLARE @cols NVARCHAR(MAX)
DECLARE @ExportId INT = (SELECT MAX(e.ExportId) FROM FSFundModel.Reporting.Export e WHERE e.FundId =@FundId AND e.NAVDate = @NAVDate)
SET @cols = STUFF(
(SELECT N',' + QUOTENAME(y) AS [text()]
FROM (SELECT DISTINCT LTRIM(RTRIM(p.PortfolioId)) AS y FROM FSFundModel.dbo.Portfolio p WHERE p.FundId =@FundId) AS Y
ORDER BY y
FOR XML PATH('')),
1, 1, N'');

--SELECT @cols

DECLARE @SQL NVARCHAR(max)

SELECT * FROM (
		SELECT LTRIM(RTRIM(a.PortfolioId)) AS PortfolioId , ISNULL(ah.[NBSReason], 'Broadly Syndicated / Other') AS NBSReason, a.MktVal FROM 
		FSFundModel.Reporting.Asset a INNER JOIN 
		ReportingScripts.Live.AssetHelper(@ExportId) ah
		ON a.AssetIntrnlId = ah.AssetIntrnlId

		WHERE a.ExportId = @ExportId
		AND a.IsActiveEndOfDay = 1) AS D
PIVOT (SUM(d.MktVal) FOR PortfolioId IN ([ARCH],[BROAD],[FSIC],[LOCUS],[RACES],[WALN])) AS P;
END

GO
EXEC sp_addextendedproperty N'VirtualFolder', N'Weekly', 'SCHEMA', N'Weekly', 'PROCEDURE', N'GetPropBreakout', NULL, NULL
GO
